
let sqlite3 = require('sqlite3').verbose();
const path = require('path');

// 创建单例数据库
let db: any = null


export async function initDb() {

  return new Promise((resolve, reject) => {
    let dbPath = path.join(__dirname, 'db.db')
    // console.log('dbPath:', dbPath)
    db = new sqlite3.Database('db.db',function(err:any){
      console.log('new Database err:',err)
    });

    db.serialize(function () {
      db.run("CREATE TABLE IF NOT EXISTS drainage (id INTEGER PRIMARY KEY AUTOINCREMENT,fileNameDate timestamp UNIQUE,fileName char(30),dataX TEXT,dataY TEXT,dataZ TEXT,createDate timestamp not null default CURRENT_TIMESTAMP)");
      db.run("CREATE TABLE IF NOT EXISTS dir (id INTEGER PRIMARY KEY AUTOINCREMENT,dirPath text UNIQUE,createDate timestamp not null default CURRENT_TIMESTAMP)");

      // var stmt = db.prepare("INSERT INTO lorem VALUES (?)");
      // for (var i = 0; i < 10; i++) {
      //   stmt.run("Ipsum " + i);
      // }
      // stmt.finalize()
    });
    resolve(void 0)
  })


}
// 增
export function insertDataToDrainageDb(fileNameDate: any, fileName: any, dataX: any, dataY: any, dataZ: any) {
  return new Promise((resolve, reject) => {
    let sql = `insert or ignore into drainage (fileNameDate,fileName,dataX,dataY,dataZ) values ('${fileNameDate}','${fileName}','${dataX}','${dataY}','${dataZ}')`
    db.run(sql, function (err: any, res: any) {
      console.log('insertDataToDrainage:', err, res);
      if (err) {
        reject(err)
      } else {
        resolve(res)
      }
    })
  })
}
export function insertDataToDirDb(dir: any) {
  let sql = `insert or ignore into dir (dirPath) values ('${dir}')`
  db.run(sql, function (err: any, res: any) {
    console.log('insertDataToDb:', err, res);
  })

}

// 删

export function deleteDataFromDrainageDb(fileNameDate: any) {
  let sql = `delete from drainage where fileNameDate = '${fileNameDate}'`
  db.run(sql, function (err: any, res: any) {
    console.log('deleteDataFromDrainage:', err, res);
  })
}

// 改

// 查

export function findDataFromDrainageDb(fileNameDate: any) {
  return new Promise((resolve, reject) => {
    let sql = `select * from drainage where fileNameDate = '${fileNameDate}'`
    db.all(sql, function (err: any, res: any) {
      resolve(res)
      console.log('findDataFromDb:', err, res);
    })
  })

}



export function findAllDataFromDrainageDb() {
  let sql = `select * from drainage`
  db.all(sql, function (err: any, res: any) {
    console.log('findAllDataFromDrainageDb:', err, res);
  })
}

export function findDataFromDrainageDbByFileNameDate(fileNameDate: string) {
  return new Promise((resolve, reject) => {
    let sql = `select * from drainage where fileNameDate = '${fileNameDate}'`

    db.get(sql, function (err: any, res: any) {
      resolve(res)
    })
  })

}

export function findLastDataFromDrainageDb() {
  return new Promise((resolve, reject) => {
    let sql = `select * from drainage order by id desc limit 1`
    db.get(sql, function (err: any,res:any) {
      console.log('findLastDataFromDrainageDb:', err);
      resolve(res)
    })
  })
}

export function findFileNameDateFromDrainageDb() {
  return new Promise((resolve, reject) => {
    let sql = `select fileNameDate from drainage where fileNameDate is not null order by fileNameDate desc`
    db.all(sql, function (err: any,res:any) {
      console.log('findLastDataFromDrainageDb res:', res);
      if(res){
        if(Array.isArray(res)){
          resolve(res.map((item:any)=> item.fileNameDate))
        }else {
          resolve([res.fileNameDate])
        }
        
      }else {
        resolve(res)
      }
      
    })
  })
}


export function findDataFromDirDb() {
  return new Promise((resolve, reject) => {
    let sql = `select * from dir order by id desc limit 1`
    db.get(sql, function (err: any, res: any) {
      console.log('findAllDataFromDirDb:', err, res);
      resolve(res)
    })
  })
}



export function closeDb() {
  if (db) {
    db.close((error: any) => {
      console.log(error);
    })
  }

}
